var express = require('express');
var fs = require('fs');
var mysql = require('mysql');
var ejs = require('ejs');
var router = express.Router();

//连接MySQL数据库
var client = mysql.createConnection({
    host: 'localhost',
    port:"3306",
    user: 'root',
    password: '123456',
    database: 'akb48'
});
//判断数据库是否连接成功
client.connect(function(err){
    if(err){
         console.log('[query] - :'+err);
         return;
    }
    console.log('[connection connect]  MySQL数据库连接成功!');
});


router.get('/', function (request, response) {
    console.log(request.body);
    client.query('select * from akb7', function (error, results) {
        response.render('akbingo/akbingo-list.ejs', {
            data: results
        });
    });
});


router.get('/search/:keyword', function (request, response) {
    const keyword = request.params.keyword;
    console.log(request.params);
    //读取模板文件
    if (keyword ) {
     try {
          const query = 'SELECT * FROM akb7 WHERE name LIKE ? OR age LIKE ? OR C LIKE ?OR waihao LIKE ?';
          const values = [`%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`];
          client.query(query, values,function (error, results) {
               if (error) {
                   console.error(error);
                   response.status(500).send('Internal Server Error');
                   return;
               }
               console.log(results);
               response.send(results );
          });
     } catch (error) {
          console.error(error);
          response.status(500).send('Internal Server Error');
      }
    }
});



router.get('/insert', function (request, response) {
    //读取模板文件
    response.render('akbingo/akbingo-insert.ejs');
});



//添加图书处理逻辑
router.post('/insert', function (request, response) {
    //声明body
    var body = request.body;
    //执行SQL语句
    client.query('insert into akb7 (id,name, age, C,waihao) VALUES (?,?, ?, ?,?)', [
         body.id,body.name, body.age, body.C,body.waihao
    ], function () {
         //响应信息
         response.redirect('/akbingo');
    });
});


router.get('/edit/:id', function (request, response) {
    console.log(request.params.id);
    client.query('select * from akb7 where id =?', [
        request.params.id
    ], function (error, result) {
        console.log(result);
        response.render('akbingo/akbingo-edit.ejs', {
            data: result[0]
        });
    });
});


router.post('/edit/:id', function (request, response) {
    //声明body
    var body = request.body;
    //执行SQL语句
    client.query('update akb7 set name=?, age=?, C=? , waihao=? where id=?',
       [body.name, body.age, body.C,body.waihao, request.params.id], function () {
         //响应信息
         response.redirect('/akbingo');
    });
});

router.get('/delete/:id', function (request, response) {
    //执行SQL语句
    client.query('delete from akb7 where id=?', [request.params.id], function () {
         //响应信息
         response.redirect('/akbingo');
    });
});

module.exports = router;